/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Business;

import Connections.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import Entities.Exhibitor;
import java.sql.Date;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author Nhit
 */
public class ExhibitorManage extends Database{
    Connection conn;
    public ExhibitorManage() {
        conn = getConn();
    }
    public ArrayList<Exhibitor> getList(){
        ArrayList<Exhibitor> arrExhibitor =  new ArrayList<Exhibitor>();
        ResultSet rs = null;
        try {
            PreparedStatement preStm = conn.prepareStatement("SELECT * FROM Exhibitor WHERE status = 0 OR status = 1");
            rs = preStm.executeQuery();
            while (rs.next()) {

                Exhibitor  objExhibitor = new Exhibitor(rs.getInt("id"), rs.getNString("firstName"),rs.getNString("lastName"),
                        rs.getNString("sex"), rs.getString("email"),rs.getString("phone"), rs.getNString("organizationName"),
                        rs.getNString("address"), rs.getNString("city"), rs.getNString("country"), rs.getString("accountBank"),
                        rs.getNString("bankName"), rs.getInt("status"), rs.getString("identityCardNumber") ,rs.getDate("registerDate"),
                        rs.getDate("modifiedDate"));

 
                arrExhibitor.add(objExhibitor);
            }
            closeConn();
        } catch (SQLException ex) {
            System.out.println("Get list error !!");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex){
            System.out.println("Error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }

        return arrExhibitor;
    }
    public ArrayList<Exhibitor> getListTrash(){
        ArrayList<Exhibitor> arrExhibitor =  new ArrayList<Exhibitor>();
        ResultSet rs = null;
        try {
            PreparedStatement preStm = conn.prepareStatement("SELECT * FROM Exhibitor WHERE status = 2");
            rs = preStm.executeQuery();
            while (rs.next()) {
               // Exhibitor(int id, String name, String sex, String email, String phone,
                  //      String address,
                  //      int status, String identityCardNumber, Date registerDate, Date modifiedDate);
                 Exhibitor  objExhibitor = new Exhibitor(rs.getInt("id"), rs.getNString("firstName"),rs.getNString("lastName"),
                        rs.getNString("sex"), rs.getString("email"),rs.getString("phone"), rs.getNString("organizationName"),
                        rs.getNString("address"), rs.getNString("city"), rs.getNString("country"), rs.getString("accountBank"),
                        rs.getNString("bankName"), rs.getInt("status"), rs.getString("identityCardNumber") ,rs.getDate("registerDate"),
                        rs.getDate("modifiedDate"));

 
                arrExhibitor.add(objExhibitor);
            }
        } catch (SQLException ex) {
            System.out.println("Get list error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }

        return arrExhibitor;
    }
    public void restoreTrash(int id){
        try {
            PreparedStatement preStm = conn.prepareStatement("UPDATE Exhibitor set status = 0 WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Restore trash error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void deleteToTrash(int id){
        try {
            PreparedStatement preStm = conn.prepareStatement("UPDATE Exhibitor set status = 2 WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Delete by id error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void deleteById(int id){
        try {
            PreparedStatement preStm = conn.prepareStatement("DELETE Exhibitor WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Empty trash error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public ResultSet listTrash(){
        ResultSet rs = null;
        try {
            PreparedStatement preStm = conn.prepareStatement("SELECT * FROM Exhibitor WHERE status = 2");
            rs = preStm.executeQuery();
        } catch (SQLException ex) {
            System.out.println("Get list exhibitor in trash error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    public void block(int id){
        
        try {
            PreparedStatement preStm = conn.prepareStatement("UPDATE Exhibitor set status = 1 WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Block fail");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void unBlock(int id){
        try {
            PreparedStatement preStm = conn.prepareStatement("UPDATE Exhibitor set status = 0 WHERE id = ?");
            preStm.setInt(1, id);
            preStm.executeUpdate();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "UnBlock fail");
            System.out.println("Unblock error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public boolean update(Exhibitor objExhibitor){
        
        boolean flag = false;
        try {
            PreparedStatement presl = conn.prepareStatement("SELECT registerDate FROM Exhibitor WHERE id = ?");
            presl.setInt(1, objExhibitor.getId());
            ResultSet rs = presl.executeQuery();
            rs.next();
            Date registerDate = rs.getDate("registerDate");
            PreparedStatement preStm = conn.prepareStatement("UPDATE Exhibitor SET "
               +"firstName = ?"
               +",lastName = ?"
               +",sex = ?"
               +",email = ?"
               +",phone = ?"
               +",organizationName = ?"
               +",address = ?"
               +",city = ?"
               +",country = ?"
               +",accountBank = ?"
               +",bankName = ?"
               +",status = ?"
               +",identityCardNumber = ?"
               +",registerDate = ?"
               +",modifiedDate = ? WHERE id = ?");
            preStm.setString(1, objExhibitor.getFirstName());
            preStm.setString(2, objExhibitor.getLastName());
            preStm.setString(3, objExhibitor.getSex());
            preStm.setString(4, objExhibitor.getEmail());
            preStm.setString(5, objExhibitor.getPhone());
            preStm.setString(6, objExhibitor.getOrganizationName());
            preStm.setString(7, objExhibitor.getAddress());
            preStm.setString(8, objExhibitor.getCity());
            preStm.setString(9, objExhibitor.getCountry());
            preStm.setString(10, objExhibitor.getAccountBank());
            preStm.setString(11, objExhibitor.getBankName());
            preStm.setInt(12, objExhibitor.getStatus());
            preStm.setString(13, objExhibitor.getIdentityCardNumber());
            preStm.setDate(14, registerDate);
            preStm.setDate(15, objExhibitor.getModifiedDate());
            preStm.setInt(16, objExhibitor.getId());
            preStm.executeUpdate();
            flag = true;
        } catch (SQLException ex) {
            System.out.println("Update error");
            flag = false;
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConn();
        return flag;
    }
    public boolean add(Exhibitor objExhibitor){
        boolean flag = false;
        try {
            PreparedStatement preStm = conn.prepareStatement("INSERT INTO Exhibitor"
               +" (firstName"
               +",lastName"
               +",sex"
               +",email"
               +",phone"
               +",organizationName"
               +",address"
               +",city"
               +",country"
               +",accountBank"
               +",bankName"
               +",status"
               +",identityCardNumber"
               +",registerDate"
               +",modifiedDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            preStm.setNString(1, objExhibitor.getFirstName());
            preStm.setNString(2, objExhibitor.getLastName());
            preStm.setNString(3, objExhibitor.getSex());
            preStm.setString(4, objExhibitor.getEmail());
            preStm.setString(5, objExhibitor.getPhone());
            preStm.setNString(6, objExhibitor.getOrganizationName());
            preStm.setNString(7, objExhibitor.getAddress());
            preStm.setNString(8, objExhibitor.getCity());
            preStm.setNString(9, objExhibitor.getCountry());
            preStm.setString(10, objExhibitor.getAccountBank());
            preStm.setNString(11, objExhibitor.getBankName());
            preStm.setInt(12, objExhibitor.getStatus());
            preStm.setString(13, objExhibitor.getIdentityCardNumber());
            preStm.setDate(14, objExhibitor.getRegisterDate());
            preStm.setDate(15, objExhibitor.getModifiedDate());
            preStm.executeUpdate();
            flag = true;
        } catch (SQLException ex) {
            System.out.println("Insert error");
            flag = false;
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConn();
        return flag;
    }
    public Exhibitor getExhibitorById(int id){
        ResultSet rs = null;
        Exhibitor objExhibitor = null;
        try {
            PreparedStatement preStm = conn.prepareStatement("SELECT * FROM Exhibitor WHERE id = ?");
            preStm.setInt(1, id);
            rs = preStm.executeQuery();
            while (rs.next()) {
//(int id, String firstName, String lastName, 
//                String sex, String email, 
//                String phone, String organizationName, 
//                String address, String city, 
//                String country, String accountBank, 
//                String bankName, int status, 
//                String identityCardNumber, Date registerDate, 
//                Date modifiedDate)
                
               objExhibitor = new Exhibitor(rs.getInt("id"), rs.getNString("firstName"),rs.getNString("lastName"),
                        rs.getNString("sex"), rs.getString("email"),rs.getString("phone"), rs.getNString("organizationName"),
                        rs.getNString("address"), rs.getNString("city"), rs.getNString("country"), rs.getString("accountBank"),
                        rs.getNString("bankName"), rs.getInt("status"), rs.getString("identityCardNumber") ,rs.getDate("registerDate"),
                        rs.getDate("modifiedDate"));
            }
        } catch (SQLException ex) {
            System.out.println("Get list error !");
            Logger.getLogger(ExhibitorManage.class.getName()).log(Level.SEVERE, null, ex);
        }

        return objExhibitor;
    }
}
